<?php
/**
 * 比对本地数据与测试环境的差异, 生成 sql 升级脚本
 * 生成的文件保存在 db/sql 下, 提交svn后, 测试环境自动执行教脚本
 */
error_reporting(0);
PHP_SAPI == 'cli' or exit('use cli');
chdir(__DIR__ . "/../..");

function query ($sqlOrConfig)
{
    static $resConfig;
    if (is_array($sqlOrConfig))
    {
        $resConfig = $sqlOrConfig;
        $link = mysql_connect($resConfig['DB_HOST'] . ':' . $resConfig['DB_PORT'], $resConfig['DB_USER'], $resConfig['DB_PWD'], true) or exit('Can not connect to db host');
        mysql_query("use " . $resConfig['DB_NAME']) or exit('Can not use db');
        mysql_query('set names utf8');
        return true;
    }
    
    $sql = @str_replace('@p_', $resConfig['DB_PREFIX'], $sqlOrConfig);
    $res = mysql_query($sql);
    if (is_bool($res))
    {
        return $res;
    }
    
    $arr = array();
    while ($row = mysql_fetch_array($res, MYSQL_ASSOC))
    {
        $arr[] = $row;
    }
    return $arr;
}

function tableSchema ($resConfig)
{
    $tables = array();
    query($resConfig);
    foreach (query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA = '{$resConfig['DB_NAME']}'") as $t)
    {
        $_name = preg_replace("/^{$resConfig['DB_PREFIX']}/", "", $t['TABLE_NAME']);
        $_sql = '';
        $_fields = [];
        
        // 创建表语句
        $_r = query("SHOW CREATE TABLE {$t['TABLE_NAME']}");
        // $_sql = iconv('UTF-8', 'GBK', $_r[0]['Create Table']) ? : $_r[0]['Create Table'];
        $_sql = $_r[0]['Create Table'];
        
        // 创建字段语句
        $_l = explode("\n", $_sql);
        for ($i = 1; $i < count($_l) - 1; $i ++)
        {
            if (preg_match("/^\s*`(.*)`/", $_l[$i]))
            {
                preg_match("/`(.*)`/", $_l[$i], $_matches);
                $_fields[$_matches[1]] = [
                        // 'sql' => trim(substr(iconv('UTF-8', 'GBK', $_l[$i]) ? : $_l[$i], 0, - 1)),
                        'sql' => trim(substr($_l[$i], 0, - 1)),
                        'md5' => md5($_l[$i])
                ];
            }
        }
        $tables[$_name] = [
                'sql' => $_sql,
                'fields' => $_fields
        ];
    }
    return $tables;
}

$_root_config = require 'config.php';
file_exists('config.dev.php') and $_root_config = array_merge($_root_config, require 'config.dev.php');
file_exists('config.product.php') and $_root_config = array_merge($_root_config, require 'config.product.php');

$config = [
        'local' => [
                'DB_HOST' => $_root_config['DB_HOST'],
                'DB_NAME' => $_root_config['DB_NAME'],
                'DB_USER' => $_root_config['DB_USER'],
                'DB_PWD' => $_root_config['DB_PWD'],
                'DB_PORT' => $_root_config['DB_PORT'],
                'DB_PREFIX' => $_root_config['DB_PREFIX']
        ],
        'remote' => [
                'DB_HOST' => 'www.ie',
                'DB_NAME' => $_root_config['DB_NAME'],
                'DB_USER' => 'bak',
                'DB_PWD' => 'bak995',
                'DB_PORT' => '33306',
                'DB_PREFIX' => $_root_config['DB_PREFIX']
        ]
];

$localTables = tableSchema($config['local']);
$remoteTables = tableSchema($config['remote']);

$diff = [];
$diffDetail = [];
$sql = [];

// 以本地数据库结构为基础, 比对远程数据库缺少的表或字段
foreach ($localTables as $_table => $_localSchema)
{
    // 缺少表
    if (! isset($remoteTables[$_table]))
    {
        $diff[] = "- $_table.*";
        $sql[] = str_replace($config['local']['DB_PREFIX'] . $_table, $config['remote']['DB_PREFIX'] . $_table, $_localSchema['sql']) . ';';
        continue;
    }
    
    foreach ($_localSchema['fields'] as $_field => $_localFieldVar)
    {
        // 字段存在
        if (isset($remoteTables[$_table]['fields'][$_field]))
        {
            // 字段定义不同
            if ($remoteTables[$_table]['fields'][$_field]['md5'] != $_localFieldVar['md5'])
            {
                $diff[] = "* $_table.$_field";
                $diff[] = "\tlocal  : {$_localFieldVar['sql']}";
                $diff[] = "\tremote : {$remoteTables[$_table]['fields'][$_field]['sql']}";
                $sql[] = "ALTER TABLE `{$config['remote']['DB_PREFIX']}{$_table}` MODIFY {$_localFieldVar['sql']};";
            }
        }
        // 缺少字段
        else
        {
            $diff[] = "- $_table.$_field";
            $sql[] = "ALTER TABLE `{$config['remote']['DB_PREFIX']}{$_table}` ADD {$_localFieldVar['sql']};";
        }
    }
}

// 以远程数据库为基础, 比对远程数据库增加的表或字段
foreach ($remoteTables as $_table => $_remoteSchema)
{
    // 增加表
    if (! isset($localTables[$_table]))
    {
        $diff[] = "+ $_table.*";
        $sql[] = "# DROP TABLE  `{$config['remote']['DB_PREFIX']}{$_table};";
        continue;
    }
    
    foreach ($_remoteSchema['fields'] as $_field => $_remoteFieldVar)
    {
        // 增加字段
        if (! isset($localTables[$_table]['fields'][$_field]))
        {
            $diff[] = "+ $_table.$_field";
            $sql[] = "# ALTER TABLE `{$config['remote']['DB_PREFIX']}{$_table}` DROP {$_field};";
            continue;
        }
    }
}

$sql or exit("schema not changed\n");

$file = __DIR__ . "/sql/" . time() . '.sql';
file_put_contents($file, implode("\n\n", $sql));

$sqlFiles = glob(__DIR__ . "/sql/*.sql");
sort($sqlFiles);
if ($sqlFiles && count($sqlFiles)>1 && md5_file($file) == md5_file(end($sqlFiles)))
{
    unlink($file);
    array_pop($sqlFiles);
    $file = end($sqlFiles);
}
echo "SCHEMA DIFF:\n" . implode("\n", $diff) . "\n\n";
echo "UPGRADE SQL:\n" . implode("\n", $sql) . "\n\n";
echo "GENERATE FILE:\n" . realpath($file) . "\n\n";